﻿<?xml version="1.0" encoding="utf-8" ?>
<document>
  <xsql name="selectAllStudent"
        cache="forever">
    SELECT
    STUDENT_ID
    ,STUDENT_NAME
    ,STUDENT_SEX
    ,STUDENT_BIRTHDAY
    ,STUDENT_PHOTO
    FROM DEMO_STUDENT
  </xsql>
  <xsql name="selectAllStudentByID">
    <include path="selectAllStudent" />
    <trim prefix="WHERE">
      <if test="studentID != null">
        STUDENT_ID=#{studentID}
      </if>
    </trim>
  </xsql>
  <xsql name="test_if">
    <include path="selectAllStudent" />
    <trim prefix="WHERE"
          trim="AND|OR">
      <if test="studentID==1">
        AND STUDENT_ID IN (1,2)
      </if>
      <else>
        AND STUDENT_ID=#{studentID}
      </else>
      <if test="studentName != null">
        AND STUDENT_NAME=#{studentName}
      </if>
    </trim>
  </xsql>
  <xsql name="test_switch"
        cache="values">
    <include path="selectAllStudent" />
    <switch test="studentName">
      <case test="=='张三'">
        STUDENT_ID=1
      </case>
      <case test="=='李四'">
        STUDENT_ID=2
      </case>
      <case test="=='王五'">
        STUDENT_ID=3
      </case>
      <default>
        STUDENT_ID IS NOT NULL
      </default>
    </switch>
  </xsql>
  <xsql name="test_foreach"
        cache="names">
    SELECT * FROM STUDENTS
    <trim prefix="WHERE"
          trim="AND|OR">
      <if test="studentSex!=null">
        AND STUDENT_SEX=#{studentSex}
      </if>
      <if test="studentNames!=null">
        AND STUDENTNAME IN(
        <foreach var="studentName"
                 in="studentNames"
                 trim=",">
          ,#{studentName}
        </foreach>
        )
      </if>
    </trim>
  </xsql>
  <xsql name="function_test"
        cache="values"
        ignores="tableName">
    SELECT * FROM
    <switch>
      <case test="left(tableName,'demo1_')">
        <expression name="$tmp1"
                    type="none">
          remove(tableName,0,6)
        </expression>
      </case>
      <case test="left(tableName,'demo2_')">
        <expression name="newTableName">
          remove(tableName,0,6)
        </expression>
      </case>
      <default>
        #{tableName, none}
      </default>
    </switch>
  </xsql>
</document>